﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;

namespace proj002
{
    class XPaidXls
    {
        private Excel.Application m_xlsApp;
        private Excel.Workbook m_xlsWorkbook;
        private Excel.Worksheet m_xlsWorksheet;
        private Excel.Range m_xlsRange;
        private System.String m_xlsPath;
        List<System.Int32> m_currencyPosition;
        List<System.Int32> m_instrumentPosition;
        XPaidXml m_XXml;
      

        public XPaidXls(System.String in_filepath)
        {
            m_xlsPath = in_filepath;  //exception  
            m_XXml = new XPaidXml(in_filepath);
           
            m_currencyPosition = new List<System.Int32>();
            m_instrumentPosition = new List<System.Int32>();
        }

        public void parsePaidXls()
        {
            //open
            //todo: error checking
            openExcel();
            parseData();
        }

        private void openExcel()
        {
            //todo: clean up resource
            object missValue = System.Reflection.Missing.Value;
            m_xlsApp = new Excel.Application();
            m_xlsWorkbook = m_xlsApp.Workbooks.Open(
                    m_xlsPath,  //file path
                    missValue, //UpdateLinks
                    true, //Readonly
                    missValue, //format
                    "", //password
                    "", //writePassword
                    missValue, //ignoreReadonly
                    Excel.XlPlatform.xlWindows, //origin
                    "\t", //delimiter,
                    false, //editable,
                    false, //notify
                    missValue, //converter,
                    missValue, //addToMru
                    missValue, //local,
                    false      //corrupt load
                );
            m_xlsWorksheet = m_xlsWorkbook.Worksheets.get_Item(1);  // the worksheet 1
            m_xlsRange = m_xlsWorksheet.UsedRange; 
        }

        private void parseData()
        {
            //look up column1 and find out currency position
            System.Int32 xlsRow = m_xlsRange.Rows.Count;
            System.Int32 xlsColumn = m_xlsRange.Columns.Count;
            Excel.Range column1Range = m_xlsRange.get_Range("A1", "A" + xlsRow);

            int rowIndex = 1;  //start from row1
            // find out how many currency in the xls file
            // find out currency position in 1 column
            foreach (object celldata in column1Range.Value2)
            {
                if (celldata != null)
                {
                    if (celldata.GetType() == typeof(string))
                    {
                        Match matchCurrency = Regex.Match((string)celldata, @"^Currency", RegexOptions.IgnoreCase);
                        if (matchCurrency.Success)
                        {
                            m_currencyPosition.Add(rowIndex);
                        }

                        Match matchInstrument = Regex.Match((string)celldata, @"^Instrument", RegexOptions.IgnoreCase);
                        if (matchInstrument.Success)
                        {
                            m_instrumentPosition.Add(rowIndex);
                        }
                    }
                }
                rowIndex++;
            }

            for (int i = 0; i < m_currencyPosition.Count; i++)   // separate currency range
            {
                System.String startCell = "A" + m_currencyPosition[i];
                System.String endCell;
                if ((i + 1) < m_currencyPosition.Count)
                {
                    endCell = "I" + (m_currencyPosition[i + 1] - 1);  //out-of index
                }
                else
                {
                    endCell = "A" + m_xlsRange.Rows.Count; ;
                }
                //parseCurrencyBlock(startCell,endCell);
                Excel.Range currencyRange = m_xlsRange.get_Range(startCell, endCell);
                parseCurrencyBlock(currencyRange);
            }
        }

        private void parseCurrencyBlock(Excel.Range currencyRange)
        {
            //Excel.Range currencyRange = m_xlsRange.get_Range(startCell, endCell);
            // find how many instruments in the currency block
            Excel.Range column1Range = currencyRange.get_Range("A1", "A" + currencyRange.Columns.Count);
            System.String currencyValue = (System.String)column1Range.Value2[1, 1];
            //write currency node
            m_XXml.setCurrencyNode(currencyValue);

            int index = 1;
            List<System.Int32> instrumentPosition = new List<System.Int32>();
            foreach (object celldata in column1Range.Value2)
            {
                if (celldata.GetType() == typeof(string))
                {
                    Match matchInstrument = Regex.Match((string)celldata, @"^Instrument", RegexOptions.IgnoreCase);
                    if (matchInstrument.Success)
                    {
                        instrumentPosition.Add(index);
                    }
                }
                index++;
            }

            // start parse instrument block
            for (int i = 0; i < instrumentPosition.Count; i++)
            {
                System.String cell1 = "A" + instrumentPosition[i];  //first row is Currency, Second is Instrument
                System.String cell2;
                if ((i + 1) < instrumentPosition.Count)
                {
                    cell2 = "I" + (instrumentPosition[i + 1] - 1);
                }
                else
                {
                    cell2 = "I" + currencyRange.Rows.Count;
                }

                Excel.Range instrumentRange = currencyRange.get_Range(cell1,cell2);
                parseInstrumentBlock(instrumentRange);
            }
        }

        private void parseInstrumentBlock(Excel.Range instrumentRange)
        {
            System.String instrumentValue = instrumentRange.Value2[1, 1];  //exception if not string
            m_XXml.setInstrumentNode(instrumentValue);
            m_XXml.saveXmlFile();

            int rowIndex = 2; //start from row2
            for (int i = 0; i < instrumentRange.Rows.Count; i++)
            {
                Excel.Range rowRange = instrumentRange.Rows.get_Item(rowIndex);
                object[] aRecord = new object[9];
                int cnt = 0;
                foreach (object element in rowRange.Value2)
                {
                    aRecord[cnt] = element;
                    cnt++;
                }
                m_XXml.setRecord(aRecord);
                rowIndex++;
            }
        }
    }
}
